﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using Newtonsoft.Json;
using System.Web.SessionState;

namespace JX_EMS
{
    public class SystemService : IRequiresSessionState
    {
        public static string ActionService(HttpContext context)
        {
            HttpRequest request = context.Request;
            string action = request["Action"];
            return ActionDo(action, request);


        }


        private static string ActionDo(string action, HttpRequest request)
        {
            StringBuilder JsonString = new StringBuilder();
            string sql = string.Empty;
            Datacon dataconn = new Datacon();
            string Method = request["Method"];
            if (action == "PAPERLIST")
            {
                sql = "select A.USER_NAME,B.c_name,C.PaperName,C.PaperID from[USER] A,[COURSE] B,[TaoTi] C where C.teacher_id=A.ID and B.teacher_id=A.ID AND B.c_id=C.c_id ORDER BY USER_NAME, c_name ASC";

                int page = Convert.ToInt32(request["page"]);
                int limit = Convert.ToInt32(request["limit"]);
                DataTable dt = dataconn.GetDataSetSql(sql).Tables[0];
                int col = dt.Rows.Count;
                dt = FormatDataTable(dt, page, limit);
                JsonString.Append("{\"code\":0,\"msg\":\"\",\"count\":");
                JsonString.Append(col);
                JsonString.Append(", \"data\":");
                JsonString.Append(JsonConvert.SerializeObject(dt));
                JsonString.Append("}");
            }

            if (action == "UserList")
            {
                if (Method == "GetTable")
                {
                    string USER_NAME = request["Name"];
                    if (USER_NAME != null)
                    {
                        sql = string.Format("select ID,LOGIN_NAME,LOGIN_PASS,USER_NAME,COMPANY,DEPARTMENT,PAPERID from [USER] where TYPE='CUS' AND USER_NAME like'%{0}%' ", USER_NAME);
                    }
                    else
                    {
                        sql = "select ID,LOGIN_NAME,LOGIN_PASS,USER_NAME,COMPANY,DEPARTMENT,PAPERID from [USER] where TYPE='CUS'";
                    }

                    int page = Convert.ToInt32(request["page"]);
                    int limit = Convert.ToInt32(request["limit"]);
                    DataTable dt = dataconn.GetDataSetSql(sql).Tables[0];
                    int col = dt.Rows.Count;
                    dt = FormatDataTable(dt, page, limit);
                    JsonString.Append("{\"code\":0,\"msg\":\"\",\"count\":");
                    JsonString.Append(col);
                    JsonString.Append(", \"data\":");
                    JsonString.Append(JsonConvert.SerializeObject(dt));
                    JsonString.Append("}");
                }

                if (Method == "Update")
                {
                    string ID = request["ID"];
                    string PaperIDList = request["PaperIDList"];
                    string USER_NAME = request["USER_NAME"];
                    string LOGIN_NAME = request["LOGIN_NAME"];
                    string LOGIN_PASS = request["LOGIN_PASS"];
                    string COMPANY = request["COMPANY"];
                    string DEPARTMENT = request["DEPARTMENT"];
                    sql = string.Format("update [USER] set PAPERID='{0}',USER_NAME='{1}', LOGIN_NAME='{2}',LOGIN_PASS='{3}',COMPANY='{4}',DEPARTMENT='{5}' where ID='{6}'", PaperIDList, USER_NAME, LOGIN_NAME, LOGIN_PASS, COMPANY, DEPARTMENT, ID);
                    bool result = dataconn.eccom(sql);
                    string msg, code;
                    if (result)
                    {
                        msg = "操作成功";
                        code = "0";
                    }
                    else
                    {
                        msg = "操作失败";
                        code = "1";
                    }
                    JsonString.Append("{\"msg\":\"");
                    JsonString.Append(msg);
                    JsonString.Append("\",\"code\":\"");
                    JsonString.Append(code);
                    JsonString.Append("\"}");


                }

                if (Method == "Delete")
                {
                    string ID = request["ID"];
                    sql = string.Format("delete from [USER] where ID='{0}'", ID);
                    string msg = dataconn.eccom(sql).ToString();
                    msg = msg == "True" ? "操作成功" : "操作失败";
                    JsonString.Append("{\"msg\":\"");
                    JsonString.Append(msg);
                    JsonString.Append("\"}");

                }
            }

            if (action == "COMPANY")
            {
                string COMPANY = request["COMPANY"];
                string DEPARTMENT = request["DEPARTMENT"];
                if (COMPANY != null)
                {

                    if (DEPARTMENT != null)
                    {
                        sql = string.Format("select [ID],[USER_NAME],COMPANY,DEPARTMENT,PAPERID FROM [USER] WHERE COMPANY IS NOT NULL AND DEPARTMENT IS NOT NULL AND COMPANY='{0}' AND DEPARTMENT='{1}'", COMPANY, DEPARTMENT);

                    }
                    else
                    {
                        sql = string.Format("select [ID],[USER_NAME],COMPANY,DEPARTMENT,PAPERID FROM [USER] WHERE COMPANY IS NOT NULL AND DEPARTMENT IS NOT NULL AND COMPANY='{0}'", COMPANY);

                    }

                }
                else
                {
                    sql = string.Format("select [ID],[USER_NAME],COMPANY,DEPARTMENT,PAPERID FROM [USER] WHERE COMPANY IS NOT NULL AND DEPARTMENT IS NOT NULL");
                }
                DataTable dt = dataconn.GetDataSetSql(sql).Tables[0];
                JsonString.Append(JsonConvert.SerializeObject(dt));

            }


            if (action == "CorrectAA")
            {
                Method = request["Method"];

                if (Method == "GetTable")
                {
                    string USERID = request["USERID"];
                    sql = @"SELECT
	                        a.ID,
	                        a.PaperID,
	                        ( SELECT PaperName FROM TaoTi WHERE PaperID = a.PaperID ) AS PaperName,
	                        a.TITLEID,
	                        a.UserID,
	                        a.USERANSWER,
	                        B.TITLE,
	                        Mark,
	                        USERMARK,
	                        ( SELECT USER_NAME FROM [user] WHERE id = a.USERID ) AS USER_NAME ,
	                        (select teacher_id from TaoTi where PaperID=a.PaperID) AS teacher_id
                        FROM
	                        USERAnsDetail A,
	                        AskAnswerProblem B,
	                        Score C 
                        WHERE
	                        type = '问答题' 
	                        AND A.TitleID = B.ID 
	                        AND a.PaperID = c.PaperID 
	                        AND a.USERID = c.UserID 
	                        and teacher_id='" + USERID + "'";

                    int page = Convert.ToInt32(request["page"]);
                    int limit = Convert.ToInt32(request["limit"]);
                    DataTable dt = dataconn.GetDataSetSql(sql).Tables[0];
                    int col = dt.Rows.Count;
                    dt = FormatDataTable(dt, page, limit);
                    JsonString.Append("{\"code\":0,\"msg\":\"\",\"count\":");
                    JsonString.Append(col);
                    JsonString.Append(", \"data\":");
                    JsonString.Append(JsonConvert.SerializeObject(dt));
                    JsonString.Append("}");

                }
                else if (Method == "SaveData")
                {
                    string ID = request["ID"];
                    string UserMark = request["USERMARK"];
                    string UserID = request["UserID"];
                    string PaperID = request["PaperID"];
                    sql = string.Format("update USERAnsDetail set UserMark='{0}' where ID='{1}' ", UserMark, ID);
                    Datacon db = new Datacon();
                    string msg = db.eccom(sql).ToString();
                    if (msg == "True")
                    {
                        msg = "保存成功！";
                        SqlParameter[] Params = new SqlParameter[2];
                        Params[0] = db.MakeInParam("@UserID", SqlDbType.VarChar, 60, UserID);
                        Params[1] = db.MakeInParam("@PaperID", SqlDbType.VarChar, 20, PaperID);
                        db.RunProc("Proc_ReflashAAScore", Params);


                    }
                    else
                    {
                        msg = "保存失败！";
                    }

                    JsonString.Append("{\"msg\":\"");
                    JsonString.Append(msg);
                    JsonString.Append("\"}");

                }



            }


            if (action == "Echarts")
            {

                Method = request["Method"];
                if (Method == "MenberCompany")
                {
                    sql = "SELECT * FROM [dbo].[Echart_MenberCompany]";
                    DataTable dt = dataconn.GetDataSetSql(sql).Tables[0];
                    int col = dt.Rows.Count;
                    JsonString.Append("{\"code\":0,\"msg\":\"\",\"count\":");
                    JsonString.Append(col);
                    JsonString.Append(", \"data\":");
                    JsonString.Append(JsonConvert.SerializeObject(dt));
                    JsonString.Append("}");

                }

                if (Method == "CompanyPercent")
                {
                    sql = "SELECT * FROM [dbo].[Echart_CompanyPercent]";
                    DataTable dt = dataconn.GetDataSetSql(sql).Tables[0];
                    int col = dt.Rows.Count;
                    JsonString.Append("{\"code\":0,\"msg\":\"\",\"count\":");
                    JsonString.Append(col);
                    JsonString.Append(", \"data\":");
                    JsonString.Append(JsonConvert.SerializeObject(dt));
                    JsonString.Append("}");

                }



            }


            return JsonString.ToString();

        }






        public static DataTable FormatDataTable(DataTable dt, int page, int limit)
        {
            DataTable _dt = dt.Clone();
            int start = (page - 1) * limit;
            int end = page * limit > dt.Rows.Count ? dt.Rows.Count : page * limit;
            for (int i = start; i < end; i++)
            {
                DataRow dr = dt.NewRow();
                dr = dt.Rows[i];
                _dt.Rows.Add(dr.ItemArray);

            }
            return _dt;

        }
    }
}